﻿using Microsoft.Data.Sqlite;
using System;
using System.IO;
using System.Linq;
using System.Reflection;

namespace EnvCheck.Services
{
    public static partial class DatabaseService
    {
        public const string DbPath = "envcheck.db";

        private static SqliteConnection CreateConn()
        {
            try
            {
                var conn = new SqliteConnection($"Data Source={DbPath};Mode=ReadWrite");
                conn.Open();
                return conn;
            }
            catch (Exception e)
            // the db file does not exist
            {
                Serilog.Log.Warning($"Failed to read Database:{e}. Creating new one...");
            }

            // just to ensure db file exists
            using (var conn = new SqliteConnection($"Data Source={DbPath}"))
            {
                conn.Open();
            }
            ExecuteSqlScript("init.sql", false);
            var retConn = new SqliteConnection($"Data Source={DbPath}");
            retConn.Open();
            return retConn;
        }

        /// <summary>
        /// 执行SQL脚本，支持嵌入资源和文件系统路径
        /// </summary>
        /// <param name="sqlFilepath">文件名或路径</param>
        /// <param name="fullPath">是否为文件系统完整路径</param>
        public static void ExecuteSqlScript(string sqlFilepath, bool fullPath)
        {
            if (fullPath)
            {
                ExecuteSqlScriptFromFile(sqlFilepath);
                return;
            }

            // 先尝试从嵌入资源读取
            string sqlScript = TryLoadSqlFromEmbeddedResource(sqlFilepath);
            if (sqlScript != null)
            {
                ExecuteSqlScriptFromString(sqlScript);
                return;
            }

            // 退回文件系统默认路径
            var sqlDir = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "sql");
            if (!Directory.Exists(sqlDir))
                throw new DirectoryNotFoundException("SQL 目录不存在: " + sqlDir);

            var fullPathFallback = Path.Combine(sqlDir, sqlFilepath);
            ExecuteSqlScriptFromFile(fullPathFallback);
        }

        private static void ExecuteSqlScriptFromFile(string sqlFilePath)
        {
            if (!File.Exists(sqlFilePath))
                throw new FileNotFoundException("SQL 文件不存在", sqlFilePath);

            var sqlScript = File.ReadAllText(sqlFilePath);
            Serilog.Log.Information($"Loaded SQL from file: {sqlFilePath}");
            ExecuteSqlScriptFromString(sqlScript);
        }

        private static void ExecuteSqlScriptFromString(string sqlScript)
        {
            using var conn = CreateConn();
            using var transaction = conn.BeginTransaction();

            try
            {
                using var cmd = conn.CreateCommand();
                cmd.Transaction = transaction;

                var sqlStatements = sqlScript.Split([';'], StringSplitOptions.RemoveEmptyEntries);

                foreach (var stmt in sqlStatements)
                {
                    var trimmed = stmt.Trim();
                    if (string.IsNullOrEmpty(trimmed)) continue;

                    cmd.CommandText = trimmed + ";";
                    var result = cmd.ExecuteNonQuery();
                    Serilog.Log.Information($"Executed SQL statement: {trimmed} Result: {result}");
                }

                transaction.Commit();
            }
            catch
            {
                transaction.Rollback();
                throw;
            }
        }

        /// <summary>
        /// 尝试从嵌入资源加载SQL脚本，找不到返回null
        /// </summary>
        /// <param name="sqlFilepath">文件名或相对路径</param>
        /// <returns>SQL脚本字符串 或 null</returns>
        private static string TryLoadSqlFromEmbeddedResource(string sqlFilepath)
        {
            var assembly = Assembly.GetExecutingAssembly();

            // 构造候选资源名列表，支持带或不带路径的资源名查找
            var resourceNames = assembly.GetManifestResourceNames();

            // 典型资源名：命名空间.路径.文件名
            // 比如 EnvCheck.sql.init.sql 或 EnvCheck.init.sql
            // 这里宽松匹配资源名结尾是 sqlFilepath 的（忽略路径分隔符差异）
            var candidate = resourceNames
                .FirstOrDefault(r =>
                    r.EndsWith(
                        sqlFilepath
                        .Replace(Path.DirectorySeparatorChar, '.')
                        .Replace(Path.AltDirectorySeparatorChar, '.'),
                        StringComparison.OrdinalIgnoreCase
                        )
                    );

            if (candidate == null)
            {
                Serilog.Log.Warning(
                    $"Embedded SQL resource not found: {sqlFilepath}");
                return null;
            }

            using var stream = assembly.GetManifestResourceStream(candidate);
            if (stream == null)
            {
                Serilog.Log.Warning(
                    $"Failed to get stream for embedded SQL resource: {candidate}");
                return null;
            }

            using var reader = new StreamReader(stream);
            var sqlScript = reader.ReadToEnd();
            Serilog.Log.Information($"Loaded SQL from embedded resource: {candidate}");
            return sqlScript;
        }
    }
}
